import os
from types import new_class

import openpyxl

'''
将多个内容一致的Excel文件合并到一个Excel文件中

小宝剑大药房（高新店）2018年销售数据.xlsx 小宝剑大药房（犀浦店）2018年销售数据.xlsx 小宝剑大药房（新津店）2018年销售数据.xlsx

'''
data_list = []
heed_column = []

def readData(filepath):
    # 读取数据
    wb = openpyxl.load_workbook(rf'{filepath}')
    sheet = wb[wb.sheetnames[0]]

    # 查看前4行
    # print("前4行数据:")
    # for row in ws[1:4]:  # 行号从1开始
    #     row_data = [cell.value for cell in row]
    #     print(row_data)
    global heed_column
    heed_column = [col.value for col in sheet[2]]
    for row in range(3, sheet.max_row + 1):
        data = [data.value for data in sheet[row] if data.value != None]
        if data:
            data_list.append(data)

    print(data_list)
    print(f"行数:{sheet.max_row}")
    print(f"列数:{sheet.max_column}")

    wb.close()
    writeData()

def writeData():
    writer = openpyxl.Workbook()
    sheet = writer.active
    sheet.title = "各药店数据汇总"
    #写入表头
    sheet.append(heed_column)
    for row in data_list:
        sheet.append(row)

    writer.save(r'Day22作业数据/药店汇总数据.xlsx')




if __name__ == '__main__':
    filepath1 = r'Day22作业数据'
    filelist = os.listdir(rf'{filepath1}')
    for file in filelist:
        readData(f'{filepath1}/{file}')

    writeData()

